JCapper Message Board

JCapper 101
-- Eliminating Horses

Home	Register Log In

Eliminating Horses

rosenowsr
12/23/2015
7:46:05 AM

Example: If I have a UDM that picks the top 2 early and the top 2 late runners (which is 4 horses),in that same UDM I want it to pick the top 3 ranked in CFA no matter how high there ranking is.
Example:
top 2 Early
top 2 Late
top 4 CFA: ranks equal 2,3,6,8
How do I get the UDM to choose the 3 lowest ranks, no matter how high they are?
Thanks

jeff
12/23/2015
10:23:34 AM

--quote:

"Example: If I have a UDM that picks the top 2 early and the top 2 late runners (which is 4 horses),in that same UDM I want it to pick the top 3 ranked in CFA no matter how high there ranking is.
Example:
top 2 Early
top 2 Late
top 4 CFA: ranks equal 2,3,6,8
How do I get the UDM to choose the 3 lowest ranks, no matter how high they are?
Thanks
"

--end quote

If I'm interpreting the question correctly - here's what I think you're really asking:

--quote:

"If I have a UDM that picks the top 2 early and the top 2 late runners (which is 4 horses), in that same UDM...

The next thing I want it to do is: Pick the top 3 in CFA from among the remaining horses not already picked as either top 2 early or top 2 late.

How do I get the UDM to do THAT?

"--end quote

Let me give that some thought first and get back to you.

-jp

.

jeff
12/24/2015
12:17:47 PM

You wrote:

--quote:

""If I have a UDM that picks the top 2 early and the top 2 late runners (which is 4 horses)""

--end quote

Q. Does your UDM always return a total of 4 horses for early and late combined (2 early and 2 late?)

Or are there races where you only want 2 or 3 for the combined early and late total?

In other words what happens when the rank=1 early horse is also rank=1 or rank=2 for late?

-jp

.

~Edited by: jeff on: 12/24/2015 at: 12:17:47 PM~

rosenowsr
12/24/2015
4:01:12 PM

I used a bad example but my UDM always picks 4 horses.

jeff
12/26/2015
2:11:24 PM

I spent some time with this the other day - and then again this morning.

I hate to admit it...

I'm able to do it using VB or VBA code.

But so far I'm not able to do it using a sql expression. Or, at least not a sql expression I can execute as a UDM Definition in the Data Window or Profile Marker.

Part of the problem is that when a sql expression is executed inside of a JCapper Module like the Data Window or Profile Marker: there is code inside of the module itself that evaluates every single field in the StarterHistory or StartersToday table. The Data Window and Profile Marker are programmed to use a single recordset. FYI the db driver used in JCapper limits each recordset to 255 columns.

So the * character has to be used or every field in the table has to be named in the UDM definition. This prevented me from using single field name evals in some of the joins and sub queries that I tried - because I kept exceeding 255 columns.

Also, those JCapper modules are programmed to raise error messages when the user attempts to execute sql statements such as i-n-s-e-r-t_i-n-t-o, a-l-t-e-r_c-o-l-u-m-n, c-r-e-a-t-e_t-a-b-l-e, d-r-o-p_t-a-b-l-e, etc.

FYI, I programmed the modules this way on purpose because I didn't want to deal with support calls, emails, message board posts, etc. from JCapper users who had accidentally deleted the starterhistory table from their database, or altered the column structure so as to render their database incompatible with JCapper.

For that reason, the next obvious strategy that I tried: embedding nested or sub queries in my sql expression - creating a temporary table - and inserting horses flagged by the sub queries into the temporary table - followed by reading the final list of selected horses from the temporary table - and (finally) deleting the temporary table from the database...

That strategy is a non starter unless I redo the programming inside of the Data Window and Profile Marker.

The next strategy that I tried was basically a "brute force" method whereby I mapped out a tree covering all of the possible cases whereby a horse can be selected by "the rules."

My interpretation of "the rules" produced three operations and the resulting psuedo-code looks something like this:

Get the "top 2 early."
Note: Seems pretty simple and straightforward right? Just get the top 2 using rank <= 2 for the slot number of the factor you are using for "early." Not so fast. You actually have to identify "rank for late" and "rank for CFA" when you pick the early horses (as further explained in the steps below.)

Get the "top 2 late."
Note: Not quite as simple as it looks. It's definitely not rank <= 2 for the slot number you are using for "late."

Here, "top 2 late" can actually end up being "rank for late" of 1, 2, 3, or 4 depending on what "rank for late" the top 2 "early" happen to have had at the time you picked them.

This means you have to identify "rank for late" of the top 2 "early" when you pick them in step 1 above - otherwise you have no way of knowing when you need to go deeper than rank <= 2 for "late."

Get the "next top 3 CFA"
Note: Not nearly as simple as it looks. It's definitely not rank <= 3 for the slot number you are using for CFA.

Here, "top 3 CFA" can actually end up being a "rank for CFA" of 1, 2, 3, 4, 5, 6, or 7 depending on what "rank for CFA" the "top 2 early" happen to have had at the time you picked them PLUS "rank for CFA" the "top 2 late" happen to have had at the time you picked those.

This means you have to identify "rank for CFA" of the "top 2 early" when you pick them in step 1 above. Further, you also have to identify "rank for CFA" of the "top 2 late" when you pick them in step 2 above.

Only then can you correctly decide how deep you have to go CFA-wise in order to get the top 3 CFA horses that haven't already been picked.

Start mapping it out if you don't believe me.

That said, the best I can offer in the form of a simple single sql UDM definition is something like this:

select * from starterhistory where (rankEARLY <= 2 or rankLATE <= 4 or rankCFA <= 7)

Note: In the above sql expression you'll need to replace "EARLY" (without the quotes) with the f-slot number of the factor you want to use for "early".

Note: In the above sql expression you'll need to replace "LATE" (without the quotes) with the f-slot number of the factor you want to use for "late".

Note: In the above sql expression you'll need to replace "CFA" (without the quotes) with the f-slot number of the factor where CFA is assigned in your sql factor setup.

At that point the UDM will flag every horse that can possibly be flagged by the "rules" as I've outlined them above.

But keep in mind, that based on field size and "rank for late" and "rank for cfa" of horses previously selected - the UDM could end up selecting as many as 11 horses.

From there, you would have to look at their ranks for "early", "late", and "cfa" - and decide how deep in the rankings you want to go.

Alternately, you might find it a better idea to break something like the above out into 3 separate UDMs... one for "early" rank <= 2... one for "late" rank <= 4... and one for "cfa" rank <= 7... and color code them using markup characters in the UDM Wizard in such a way so that "early", "late", and "cfa" are distinguishable at a glance.

Also, if you start mapping out the tree of possible horses that can be selected by the rules: You it's pretty easy to come up with eliminations for some of the cases where a horse cannot be selected without having to map out the entire tree. Spend some time doing that and it's not terribly hard to get the number of total horses selected down to something that's manageable...

BUT, unless you map out the entire tree and code out all of the possible eliminations: You're still going to have to manually comb through the list of flagged horses and decide how deep to go late-wise and cfa-wise.

Sadly that's what I've been able to come up with so far.

Who knows? Maybe there's a sql guru out there somewhere who can do this in a sql expression that can be used as the basis for a UDM Definition?

-jp

.

~Edited by: jeff on: 12/26/2015 at: 2:11:24 PM~

jeff
12/26/2015
3:39:22 PM

In my above post I said that I can do this in VB code.

To that end, the new program upgrade I am currently working on contains a new screen in the Enhanced Settings Module where you can define something called a "Priority List."

The screen is an interface that lets you define and store the parameters (factor names, rankings, etc.) that make up the multiple sets of rules for exactly the type of thing being talked about in this thread.

The JCapper.mdb file in the new program update contains a new table called MagicNumbers where the user defined sets rules are stored.

During a Calc Races, the active user defined stored sets of rules are read from the table and evaluated one at a time - and the saddle cloth numbers of horses flagged by each individual set of user defined rules are identified.

From there, when the SQL UDM Plays Report is generated: The saddle cloth numbers flagged during a Calc Races are displayed in the correct order:

1st horse flagged by the rules... through the nth horse flagged by the rules.

I currently have this working for about a dozen factors in the program.

Here's a screenshot of today's HAW R1 from my own SQL UDM Plays Report:

In the above screenshot, reading left to right, and starting immediately to the right of the highlighted UDM name for the horse flagged by the UDM:

The displayed saddle cloth numbers tell me at a glance that the 6 is the UPR rank=1 horse flagged by the UDM.

The ~ character indicates a break to the next set of saddle cloth numbers for horses not flagged by the UDM. Here, the 3 is UPR rank=2, the 1 is UPR rank=3, the 2 is UPR rank=4, and the 5 is UPR rank=5. Those are the remaining UPR 1-5 horses covered by the stored rules set displayed in the next screenshot.

The ... characters to the right of that indicate a break before saddle cloth numbers for the next set of rules are displayed.

FYI, the next set of my stored rules after UPR 1-5 sorted by priority is JPRCLass 1-5. The saddle cloth for the 4 horse is displayed here. (Hint: It's the lone JPRClass rank 1-5 horse not identified by any of the previously executed sets of rules.)

So that you can see what it looks like here's a screenshot of the interface where I've pulled up a stored set of rules based on my own UPR 1-5:

That said, I'm currently working on the ability to use stored sql expressions as the method behind the user defined rules instead of the single factor minRank/maxRank interface shown above.

Creating the ability to do that means every data field in the StarterHistory table becomes fair game for this.

-jp

.

~Edited by: jeff on: 12/26/2015 at: 3:39:22 PM~

rosenowsr
12/27/2015
8:24:43 PM

Thanks Jeff, sorry to take up so much of your time, I currently use 3 udm's to complete the selection.

The new update looks great, has a lot of potential. Can't wait to try it.

JCapper Message Board

JCapper 101 -- Eliminating Horses

JCapper 101
-- Eliminating Horses